package com.ssy.lingxi.report.dao.purchase;

import cn.hutool.core.collection.CollectionUtil;
import com.ssy.lingxi.report.entity.report.PurchaseDayReportDO;
import com.ssy.lingxi.report.model.bo.tally.statistics.PlatformPurchaseStatisticsTallyVO;
import com.ssy.lingxi.report.model.bo.tally.statistics.PurchaseBiddingStatisticsTallyVO;
import com.ssy.lingxi.report.model.bo.tally.statistics.PurchaseOnlineBiddingStatisticsTallyVO;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Service;

import javax.annotation.Resource;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.stream.Collectors;

/**
 *  采购竞价DAO
 * @author ds
 * @date 2021/6/3
 * @version 2.0.0
 */
@Service
public class BiddingDAO {

    @Resource
    @Qualifier("purchaseJdbcTemplate")
    private JdbcTemplate jdbcTemplate;

    /**
     * 采购竞价-统计数量
     * [采购竞价]
     * @return
     */
    public List<PurchaseBiddingStatisticsTallyVO> getStatisticsCount() {
        StringBuilder sql = new StringBuilder();
        sql.append(" select ")
                .append(" create_member_id memberId,create_member_role_id roleId,")
                .append(" sum(case when (external_state = 1 and interior_state=1 )then 1 else 0 end) toBeCommitValifyCount,")
                .append(" sum(case when (external_state = 1 and interior_state=2 )then 1 else 0 end) toBeValifyStep1Count,")
                .append(" sum(case when (interior_state = 3 )then 1 else 0 end) toBeValifyStep2Count,")
                .append(" sum(case when (interior_state = 4 )then 1 else 0 end) toBeCommitCount,")
                .append(" sum(case when (external_state = 4 )then 1 else 0 end) toBeValifyCount,")
                .append(" sum(case when (external_state = 5 )then 1 else 0 end) toBeBiddingCount,")
                .append(" sum(case when (external_state = 6 and audit_interior_state in (12,16,17) )then 1 else 0 end) toBeCommitValifyResultCount,")
                .append(" sum(case when (external_state = 6 and audit_interior_state = 13)then 1 else 0 end) toBeValifyResultStep1Count,")
                .append(" sum(case when (external_state = 6 and audit_interior_state = 14)then 1 else 0 end) toBeValifyResultStep2Count,")
                .append(" sum(case when (external_state = 6 and audit_interior_state = 15)then 1 else 0 end) toBeConfirmResultCount")
                .append(" FROM pu_bidding ")
                .append(" group by create_member_id,create_member_role_id;");
        return jdbcTemplate.query(sql.toString(), new BeanPropertyRowMapper<>(PurchaseBiddingStatisticsTallyVO.class));
    }



    /**
     * 指定会员的竞价单数-统计数量
     * [在线竞价][待竞价报名]
     * @return
     */
    public List<PurchaseOnlineBiddingStatisticsTallyVO> getOnlinePointMemberStatisticsCount() {
        StringBuilder sql = new StringBuilder();
        sql.append(" select ")
                .append(" dm.member_id memberId,dm.role_id roleId,")
                .append(" sum(case when (external_state = 3 )then 1 else 0 end) toBeBiddingSignupCount")
                .append(" FROM pu_bidding b inner join pu_bidding_demand_member dm on b.id=dm.bidding_id")
                .append(" group by dm.member_id,dm.role_id;");
        return jdbcTemplate.query(sql.toString(), new BeanPropertyRowMapper<>(PurchaseOnlineBiddingStatisticsTallyVO.class));
    }

    /**
     * 公开竞价的竞价单数-统计数量
     * [在线竞价][待竞价报名]
     * @return
     */
    public List<PurchaseOnlineBiddingStatisticsTallyVO> getOnlinePublicStatisticsCount() {
        StringBuilder sql = new StringBuilder();
        sql.append(" select ")
                .append(" o.member_id memberId,o.member_role_id roleId,")
                .append(" sum(case when (b.external_state = 11 )then 1 else 0 end) toBeBiddingSignupCount")
                .append(" FROM pu_bidding b inner join pu_on_line_bidding o on b.id=o.bidding_id ")
                .append(" WHERE o.member_id is not null")
                .append(" group by o.member_id,o.member_role_id;");
        return jdbcTemplate.query(sql.toString(), new BeanPropertyRowMapper<>(PurchaseOnlineBiddingStatisticsTallyVO.class));
    }

    /**
     * 待竞价-统计数量
     * [在线竞价][待竞价]
     * @return
     */
    public List<PurchaseOnlineBiddingStatisticsTallyVO> getOnlineStayBiddingStatisticsCount() {
        StringBuilder sql = new StringBuilder();
        sql.append(" select ")
                .append(" o.member_id memberId,o.member_role_id roleId,")
                .append(" sum(case when (b.external_state = 5 )then 1 else 0 end) toBeBiddingCount")
                .append(" FROM pu_bidding b inner join pu_on_line_bidding o on b.id=o.bidding_id  ")
                .append(" WHERE o.member_id is not null")
                .append(" group by o.member_id,o.member_role_id;");
        return jdbcTemplate.query(sql.toString(), new BeanPropertyRowMapper<>(PurchaseOnlineBiddingStatisticsTallyVO.class));
    }

    /**
     * [在线竞价][汇总]
     * @return
     */
    public List<PurchaseOnlineBiddingStatisticsTallyVO> getOnlineStatisticsCount() {
        Map<String,PurchaseOnlineBiddingStatisticsTallyVO> map=null;
        List<PurchaseOnlineBiddingStatisticsTallyVO> onlinePointMemberStatisticsCount = getOnlinePointMemberStatisticsCount();
        if(CollectionUtil.isNotEmpty(onlinePointMemberStatisticsCount)){
            map = onlinePointMemberStatisticsCount.stream().collect(Collectors.toMap(o -> o.getMemberId() + ":" + o.getRoleId(), o -> o));
        }
        List<PurchaseOnlineBiddingStatisticsTallyVO> onlinePublicStatisticsCount = getOnlinePublicStatisticsCount();
        if(CollectionUtil.isNotEmpty(onlinePublicStatisticsCount)){
            if(map==null){map=new HashMap<>();}
            for(PurchaseOnlineBiddingStatisticsTallyVO o :onlinePublicStatisticsCount){
                String key=o.getMemberId() + ":" + o.getRoleId();
                PurchaseOnlineBiddingStatisticsTallyVO onlineBiddingVO = map.get(key);
                if(onlineBiddingVO!=null){
                    onlineBiddingVO.setToBeBiddingSignupCount(onlineBiddingVO.getToBeBiddingSignupCount()+o.getToBeBiddingSignupCount());
                }else{
                    map.put(key,o);
                }
            }
        }
        List<PurchaseOnlineBiddingStatisticsTallyVO> onlineStayBiddingStatisticsCount = getOnlineStayBiddingStatisticsCount();
        if(CollectionUtil.isNotEmpty(onlineStayBiddingStatisticsCount)){
            if(map==null){map=new HashMap<>();}
            for(PurchaseOnlineBiddingStatisticsTallyVO o :onlinePublicStatisticsCount){
                String key=o.getMemberId() + ":" + o.getRoleId();
                PurchaseOnlineBiddingStatisticsTallyVO onlineBiddingVO = map.get(key);
                if(onlineBiddingVO!=null){
                    onlineBiddingVO.setToBeBiddingCount(onlineBiddingVO.getToBeBiddingCount()+o.getToBeBiddingCount());
                }else{
                    map.put(key,o);
                }
            }
        }
        return map!=null? new ArrayList<>(map.values()) :null;
    }

    /**
     * 平台采购统计[待审核采购竞价]
      * @return
     */
    public PlatformPurchaseStatisticsTallyVO getPlatformStatisticsCount() {
        StringBuilder sql = new StringBuilder();
        sql.append(" select ")
                .append(" count(*) toBeVerifyBiddingCount")
                .append(" FROM pu_bidding where external_state=2 ;");
        return jdbcTemplate.queryForObject(sql.toString(), new BeanPropertyRowMapper<>(PlatformPurchaseStatisticsTallyVO.class));
    }

    /**
     * 采购统计[平台审核通过的]
     * @return
     */
    public List<PurchaseDayReportDO> getPlatformExaminedStatisticsCount(Long startTime, Long endTime) {
        StringBuilder sql = new StringBuilder();
        sql.append(" select ")
                .append(" inq.create_member_id memberId,inq.create_member_role_id roleId,count(*) biddingNum")
                .append(" FROM pu_bidding inq")
                .append(" inner join pu_external_bidding_log log on inq.id=log.bidding_id ")
                .append(" where log.state=3 ")
                .append(" and log.create_time>=" + startTime)
                .append(" and log.create_time<=" + endTime)
                .append(" group by inq.create_member_id,inq.create_member_role_id;");
        return jdbcTemplate.query(sql.toString(), new BeanPropertyRowMapper<>(PurchaseDayReportDO.class));
    }
}
